package com.dx.mes.service.plan.service;

import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import com.dx.mes.common.transcoder.util.*;
import com.dx.mes.facade.plan.tx.Fdpaplyrs.FdpaplyrsI;
import org.apache.commons.lang.StringUtils;
import org.hibernate.LockMode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import com.dx.common.table.ppt.Bis_data;
import com.dx.common.table.ppt.Bis_mdl_def;
import com.dx.common.table.ppt.Bis_mtrl;
import com.dx.common.table.ppt.Ret_prd_in;
import com.dx.common.table.ppt.Ret_wo;
import com.dx.common.table.ppt.Ret_wo_bom;
import com.dx.common.table.ppt.Ret_wo_bomId;
import com.dx.mes.common.dao.IBisDataDao;
import com.dx.mes.common.dao.IBisMdlDefDao;
import com.dx.mes.common.dao.IBisMtrlDao;
import com.dx.mes.common.dao.IRetPrdInDao;
import com.dx.mes.common.dao.IRetWODao;
import com.dx.mes.common.dao.IRetWoBomDao;
import com.dx.mes.facade.plan.service.IFdpaplywoService;
import com.dx.mes.facade.plan.tx.Fdpaplywo.FdpaplywoI;
import com.dx.mes.facade.plan.tx.Fdpaplywo.FdpaplywoIA;
import com.dx.mes.facade.plan.tx.Fdpaplywo.FdpaplywoIB;
import com.dx.mes.facade.plan.tx.Fdpaplywo.FdpaplywoO;
import com.dx.mes.facade.plan.tx.Fdpaplywo.FdpaplywoOA;
import com.dx.mes.facade.plan.tx.Fdpaplywo.FdpaplywoOB;
import com.dx.mes.facade.plan.tx.Fdpaplywo.FdpaplywoOC;

import static com.dx.mes.common.bean.def.GenericDef.*;

@Service("fdpaplywoService")
@SuppressWarnings({"unchecked"})
public class Fdpaplywoservice implements IFdpaplywoService {
    private static final String TX_NAME = T_FDPAPLYWO;
    private LogUtils logUtils;

    @Autowired
    private IRetWODao retWODao;
    @Autowired
    private IBisMdlDefDao bisMdlDefDao;
    @Autowired
    private IRetPrdInDao retPrdInDao;
    @Autowired
    private IRetWoBomDao retWoBomDao;
    @Autowired
    private IBisDataDao bisDataDao;
    @Autowired
    private IBisMtrlDao bisMtrlDao;
    @Override
    public String subMainProc(String evt_no, String strInTrx) {
        AppContext.clear();
        AppContext.setCurrServiceName(Fdpaplywoservice.class.getSimpleName());
        AppContext.setCurrEventNumber(evt_no);
        logUtils = new LogUtils(Fdpaplywoservice.class);
        logUtils.info("[InTrx:" + strInTrx + "]");
        String strOutTrx = "";
        FdpaplywoI inTrx = new FdpaplywoI();
        FdpaplywoO outTrx = new FdpaplywoO();
        outTrx.setTrx_id(TX_NAME);
        outTrx.setType_id(TRX_OUT);
        outTrx.setRtn_code(String.valueOf(RETURN_CODE_SET_ERROR));

        try {
            inTrx = JacksonUtil.fromJson(strInTrx, FdpaplywoI.class);
            char action_flg = inTrx.getAction_flg().charAt(0);
            switch (action_flg) {
                case ACTION_FLG_QUERY:
                    queryByConditionFnc(inTrx, outTrx);
                    break;
                case ACTION_FLG_ADD:
                    addOrUpdateFnc(inTrx, outTrx);
                    break;
                case ACTION_FLG_DELETE:
                    deleteFnc(inTrx, outTrx);
                    break;
                case ACTION_FLG_CHECK:
                    checkOutWoStat(inTrx, outTrx);
                    break;
                case ACTION_FLG_RELEASE:
                    releaseFnc(inTrx, outTrx);
                    break;
                case ACTION_FLG_BINDSOID:
                    bindFnc(inTrx, outTrx);
                    break;
                case ACTION_FLG_INQUIRE:
                    queryByMtrl(inTrx, outTrx);
                    break;
                case 'L':
                    findAllList(inTrx, outTrx);
                    break;
                case 'V':
                    findWoIdList(outTrx);
                    break;
                case 'M':
                    queryByDense(inTrx, outTrx);
                    break;
                case 'W':
                    getInfoByWoId(inTrx, outTrx);
                    break;
                case 'F':
                    bindMtrlFnc(inTrx, outTrx);
                    break;
                case 'T':
                    queryByMtrlCate(inTrx, outTrx);
                    break;
                case 'S':
                    sapWoMtrl(inTrx, outTrx);
                    break;
                default:
                    errActionFnc(outTrx);
            }
            if (RETURN_CODE_OK.equals(outTrx.getRtn_code())) {
                retWODao.flushCurSession();
            } else {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            }
        } catch (Exception ex) {
            outTrx.setRtn_code(RETURN_CODE_UNKNOWN);
            outTrx.setRtn_mesg(ex.toString());
            logUtils.error(Util.stackTraceToString(ex));
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        } finally {
            strOutTrx = JacksonUtil.toJSONStr(outTrx);
            logUtils.info("[OutTrx:" + strOutTrx + "]");
        }
        return strOutTrx;
    }

    public void queryByConditionFnc(FdpaplywoI inTrx, FdpaplywoO outTrx) {
        List<FdpaplywoIA> iary = inTrx.getIary();
        String woTyp = "W";
        List<FdpaplywoOA> oaryList = new ArrayList<FdpaplywoOA>();
        String hql = "FROM Ret_wo where 1 = 1 ";
        if (iary.size()>0) {
            FdpaplywoIA fdpaplywoIA = iary.get(0);
            String cusIdFk = fdpaplywoIA.getCus_id_fk();
            String mdlIdFk = fdpaplywoIA.getMdl_id_fk();
            String woCate = fdpaplywoIA.getWo_cate();
            String woId = fdpaplywoIA.getWo_id();
            String woStat = fdpaplywoIA.getWo_stat();
            String woPrty = fdpaplywoIA.getWo_prty();
            String pathIdFk = fdpaplywoIA.getPath_id_fk();
            String pathVerFk = fdpaplywoIA.getPath_ver_fk();

            if (woId != null && woId.length() != 0) {
                hql = hql + " and wo_id ='" + woId + "'";
            }
            if (cusIdFk != null && cusIdFk.length() != 0) {
                hql = hql + " and cus_id_fk ='" + cusIdFk + "'";
            }
            if (mdlIdFk != null && mdlIdFk.length() != 0) {
                hql = hql + " and mdl_id_fk ='" + mdlIdFk + "'";
            }
            if (woCate != null && woCate.length() != 0) {
                hql = hql + " and wo_cate ='" + woCate + "'";
            }
            if (woStat != null && woStat.length() != 0) {
                hql = hql + " and wo_stat ='" + woStat + "'";
            }
            if (woPrty != null && woPrty.length() != 0) {
                hql = hql + " and wo_prty ='" + woPrty + "'";
            }
            if (!StringUtils.isEmpty(pathIdFk)) {
                hql = hql + " and path_id_fk = '" + pathIdFk + "'";
            }
            if (!StringUtils.isEmpty(pathVerFk)) {
                hql = hql + " and path_ver_fk = '" + pathVerFk + "'";
            }
        }

        hql = hql + " and wo_typ = '" + woTyp + "'";
        List<Ret_wo> ret_woList = retWODao.find(hql);
        for (Ret_wo ret_wo:ret_woList) {
        	FdpaplywoOA oaryA = setOaryA(ret_wo);
            oaryList.add(oaryA);
        }
        outTrx.setOary(oaryList);
        outTrx.setTbl_cnt(oaryList.size());
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }
    
    public void addOrUpdateFnc(FdpaplywoI inTrx, FdpaplywoO outTrx){

        Timestamp currentTime = Util.getCurrentTimestamp();
        List<FdpaplywoIA> list =  inTrx.getIary();
        List<Ret_wo>  retWoList = new ArrayList<>();
        for(FdpaplywoIA fdpaplywoIA : list){
            //获取产品信息
            Bis_mdl_def bis_mdl_def = bisMdlDefDao.get(fdpaplywoIA.getMdl_id_fk());
            if(bis_mdl_def == null){
                outTrx.setRtn_code(E_BIS_MDL_DEF + E_READ_NOT_FOUND + "");
                outTrx.setRtn_mesg("未找到产品代码[" + fdpaplywoIA.getMdl_id_fk() + "]的信息");
                return;
            }
            //根据工单代码获取工单信息
            Ret_wo ret_wo = retWODao.getWithLock(fdpaplywoIA.getWo_id(), LockMode.UPGRADE);
            if(ret_wo == null){
                ret_wo = new Ret_wo();
                ret_wo.setWo_id(fdpaplywoIA.getWo_id());
                //设置为W
                ret_wo.setWo_typ("W");
                ret_wo.setOk_ratio(bis_mdl_def.getOk_ratio());
                ret_wo.setFrom_thickness(bis_mdl_def.getFrom_thickness());
                ret_wo.setTo_thickness(bis_mdl_def.getTo_thickness());
                ret_wo.setCus_mtrl_id_fk(bis_mdl_def.getCus_mdl_id());
                //根据产品维护的工艺路线设定
                ret_wo.setPath_id_fk(bis_mdl_def.getPath_id_fk());
                ret_wo.setPath_ver_fk(bis_mdl_def.getPath_ver_fk());
                //自定义工单的工艺路线
                if (!Util.isSpaceCheck(fdpaplywoIA.getPath_id_fk())) {
                    ret_wo.setPath_id_fk(fdpaplywoIA.getPath_id_fk());
                }
                if (!Util.isSpaceCheck(fdpaplywoIA.getPath_ver_fk())) {
                    ret_wo.setPath_ver_fk(fdpaplywoIA.getPath_ver_fk());
                }
                ret_wo.setCrt_timestamp(currentTime);
                ret_wo.setWo_stat(WO_STATUS_WAIT);
                ret_wo.setRl_prd_qty(0);
                ret_wo.setComp_prd_qty(0);
                ret_wo.setWh_in_prd_qty(0);
                ret_wo.setScrp_prd_qty(0);
                ret_wo.setRl_prd_qty(0);
                ret_wo.setComp_prd_qty(0);
                ret_wo.setWh_in_prd_qty(0);
                ret_wo.setWh_out_prd_qty(0);
                ret_wo.setScrp_prd_qty(0);
                ret_wo.setWo_prty("9");
                ret_wo.setCrt_usr(inTrx.getEvt_usr());
            }else{
                if (WO_STATUS_RLSE.equals(ret_wo.getWo_stat()) && ret_wo.getRl_prd_qty() > 0) {//工单已经下线
                    if (fdpaplywoIA.getPln_prd_qty() < ret_wo.getRl_prd_qty()) {
                        outTrx.setRtn_code(String.valueOf(RETURN_CODE_SET_ERROR) + "");
                        outTrx.setRtn_mesg("计划投产数量[" + fdpaplywoIA.getPln_prd_qty()
                                + "]小于实际下线数量[" + ret_wo.getRl_prd_qty()
                                + "]，请重新设置");
                        return;
                    }
                }else{
                    ret_wo.setOk_ratio(bis_mdl_def.getOk_ratio());
                    ret_wo.setFrom_thickness(bis_mdl_def.getFrom_thickness());
                    ret_wo.setTo_thickness(bis_mdl_def.getTo_thickness());
                    ret_wo.setCus_mtrl_id_fk(bis_mdl_def.getCus_mdl_id());
                    //根据产品维护的工艺路线设定
                    ret_wo.setPath_id_fk(bis_mdl_def.getPath_id_fk());
                    ret_wo.setPath_ver_fk(bis_mdl_def.getPath_ver_fk());
                    //自定义工单的工艺路线
                    if (!Util.isSpaceCheck(fdpaplywoIA.getPath_id_fk())) {
                        ret_wo.setPath_id_fk(fdpaplywoIA.getPath_id_fk());
                    }
                    if (!Util.isSpaceCheck(fdpaplywoIA.getPath_ver_fk())) {
                        ret_wo.setPath_ver_fk(fdpaplywoIA.getPath_ver_fk());
                    }
                }
            }
            ret_wo.setCus_id_fk(fdpaplywoIA.getCus_id_fk());
            ret_wo.setMdl_id_fk(fdpaplywoIA.getMdl_id_fk());
            ret_wo.setWo_cate(fdpaplywoIA.getWo_cate());
            ret_wo.setPln_prd_qty(fdpaplywoIA.getPln_prd_qty());
            if(!Util.isSpaceCheck(fdpaplywoIA.getPln_stb_date())){
                if(Util.isSpaceCheck(fdpaplywoIA.getPln_stb_time())){
                    ret_wo.setPln_stb_timestamp(Timestamp.valueOf(fdpaplywoIA.getPln_stb_date() + " 00:00:00"));
                }else{
                    ret_wo.setPln_stb_timestamp(Timestamp.valueOf(fdpaplywoIA.getPln_stb_date() + " " + fdpaplywoIA.getPln_stb_time()));
                }
            }else{
                ret_wo.setPln_stb_timestamp(Util.getCurrentTimestamp());
            }
            if(!Util.isSpaceCheck(fdpaplywoIA.getPln_cmp_date())){
                if(Util.isSpaceCheck(fdpaplywoIA.getPln_cmp_time())){
                    ret_wo.setPln_cmp_timestamp(Timestamp.valueOf(fdpaplywoIA.getPln_cmp_date() + " 00:00:00"));
                }else{
                    ret_wo.setPln_cmp_timestamp(Timestamp.valueOf(fdpaplywoIA.getPln_cmp_date() + " " + fdpaplywoIA.getPln_cmp_time()));
                }
            }else{
                ret_wo.setPln_cmp_timestamp(Util.getCurrentTimestamp());
            }
            ret_wo.setWo_note(fdpaplywoIA.getWo_note());
            ret_wo.setWo_dsc(fdpaplywoIA.getWo_dsc());
            ret_wo.setEvt_usr(inTrx.getEvt_usr());
            ret_wo.setEvt_timestamp(currentTime);
            ret_wo.setFab_site_fk(fdpaplywoIA.getFab_site_fk());
            ret_wo.setFab_id_fk(fdpaplywoIA.getFab_id_fk());
            ret_wo.setDest_shop(fdpaplywoIA.getDest_shop());
            ret_wo.setPlant_id(fdpaplywoIA.getLine_id());//记录SAP线别
            retWoList.add(ret_wo);

            if(_YES.equals(inTrx.getOpi_ope_flg())){
                List<FdpaplywoOA> oaryList = new ArrayList<FdpaplywoOA>();
                FdpaplywoOA oaryA = setOaryA(ret_wo);
                oaryList.add(oaryA);
                outTrx.setOary(oaryList);
            }
        }
        retWODao.saveOrUpdateAll(retWoList);
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }

    //前台根据stat状态 设置输入框的可编辑性
    public void checkOutWoStat(FdpaplywoI inTrx, FdpaplywoO outTrx) {
        List<FdpaplywoIA> iary = inTrx.getIary();
        FdpaplywoIA fdpaplywoIA = iary.get(0);
        List<FdpaplywoOA> oaryList = new ArrayList<FdpaplywoOA>();

        String woId = fdpaplywoIA.getWo_id();
        String hql = "FROM Ret_wo where 1 = 1 ";
        if (woId != null && woId.length() != 0) {
            hql = hql + "and wo_id='" + woId + "'";
        }
        List<Ret_wo> ret_wos = retWODao.find(hql);
        for (Ret_wo ret_wo : ret_wos) {
            FdpaplywoOA fdpaplywoOA = new FdpaplywoOA();
            String woStat = ret_wo.getWo_stat();
            fdpaplywoOA.setWo_stat(woStat);
            oaryList.add(fdpaplywoOA);
        }
        outTrx.setOary(oaryList);
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }

    //下线 ：需要改变工单rl_prd_qty，ret_prd_info 的状态吗
    public void releaseFnc(FdpaplywoI inTrx, FdpaplywoO outTrx) {
        String evtUser = inTrx.getEvt_usr();
        Timestamp currentTime = Util.getCurrentTimestamp();
        List<FdpaplywoIA> iary = inTrx.getIary();
        FdpaplywoIA fdpaplywoIA = iary.get(0);
        String woId = fdpaplywoIA.getWo_id();
        Ret_wo ret_wo = retWODao.get(woId);
        String soId = ret_wo.getSo_id();
        if (soId == null || soId.length() == 0) {
            outTrx.setRtn_code(E_FDPAPLYWO_RLSE_ERROR + "");
            outTrx.setRtn_mesg("请先绑定内部订单，再下线！");
            return;
        }
        if (!WO_STATUS_WAIT.equals(ret_wo.getWo_stat())) {
            outTrx.setRtn_code(E_FDPAPLYWO_RLSE_ERROR + "");
            outTrx.setRtn_mesg("工单状态只有为[" + WO_STATUS_WAIT + "]才能下线," + "工单[" + woId + "]的状态为" + ret_wo.getWo_stat());
            return;
        }
        //下线 状态+rl_prd_qty
        ret_wo.setWo_stat(WO_STATUS_RLSE);
        Integer plnPrdQty = ret_wo.getPln_prd_qty();
        ret_wo.setRl_prd_qty(plnPrdQty);
        ret_wo.setEvt_usr(evtUser);
        ret_wo.setEvt_timestamp(currentTime);
        retWODao.update(ret_wo);

        //返回状态
        List<FdpaplywoOA> oaryList = new ArrayList<FdpaplywoOA>();
    	FdpaplywoOA oaryA = setOaryA(ret_wo);
        oaryList.add(oaryA);
        outTrx.setOary(oaryList);
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }

    public void deleteFnc(FdpaplywoI inTrx, FdpaplywoO outTrx) {
        List<FdpaplywoIA> iary = inTrx.getIary();
        FdpaplywoIA fdpaplywoIA = iary.get(0);
        String woId = fdpaplywoIA.getWo_id();
        Ret_wo ret_wo = retWODao.get(woId);
        if (ret_wo == null) {
            outTrx.setRtn_code(E_FDPAPLYWO_NOT_FOUND + "");
            outTrx.setRtn_mesg("record not found");
            return;
        }
        if (WO_STATUS_WAIT.equals(ret_wo.getWo_stat()) && ret_wo.getRl_prd_qty() == 0) {
            retWODao.delete(ret_wo);
        } else {
            outTrx.setRtn_code(E_FDPAPLYWO_STAT_ERROR + "");
            outTrx.setRtn_mesg("工单[" + woId + "]的状态为" + ret_wo.getWo_stat() + ",不可删除！");
            return;
        }
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }

    public void bindFnc(FdpaplywoI inTrx, FdpaplywoO outTrx) {
        String evtUsr = inTrx.getEvt_usr();
        Timestamp currentTime = Util.getCurrentTimestamp();
        List<FdpaplywoIA> iary = inTrx.getIary();
        FdpaplywoIA fdpaplywoIA = iary.get(0);
        //工单订单ID
        String woId = fdpaplywoIA.getWo_id();
        //内部订单ID
        String woId2 = fdpaplywoIA.getWo_id2();
        //获取工单信息
        Ret_wo ret_wo = retWODao.getWithLock(woId, LockMode.UPGRADE);
        if (ret_wo == null) {
            outTrx.setRtn_code(E_FDPAPLYWO_NOT_FOUND + "");
            outTrx.setRtn_mesg("未找到工单代码[" + woId + "]的信息");
            return;
        }
        //获取内部订单信息
        Ret_wo ret_so = retWODao.get(woId2);
        if(ret_so == null){
            outTrx.setRtn_code(E_FDPAPLYWO_NOT_FOUND + "");
            outTrx.setRtn_mesg("未找到内部订单号[" + woId2 + "]的信息");
            return;
        }
        if (!ret_wo.getWo_cate().equals(ret_so.getWo_cate())) {
            outTrx.setRtn_code(E_FDPAPLYWO_WO_CATE_MISMATCH + "");
            outTrx.setRtn_mesg("工单代码[" + woId + "]与客户订单号[" + ret_so.getSo_id() + "]的类型不同,无法绑定");
            return;
        }
        
        if(!ret_wo.getMdl_id_fk().equals(ret_so.getMdl_id_fk())){
            outTrx.setRtn_code(E_FDPAPLYWO_MDL_ID_MISMATCH + "");
            outTrx.setRtn_mesg("工单代码[" + woId + "]与客户订单号[" + ret_so.getSo_id() + "]的产品代码不同,无法绑定");
            return;
        }

        String soID = ret_wo.getSo_id();
        Integer rlPrdQty = ret_wo.getRl_prd_qty();
        if (rlPrdQty > 0 && null != soID && soID.length() != 0) {
            outTrx.setRtn_code(E_FDPAPLYWO_OTHER_ERROR + "");
            outTrx.setRtn_mesg("工单[" + woId + "]已绑定过客户订单[" + soID + "]并已下线，不可再次绑定！");
            return;
        }
       //不卡控绑定数量
       /* Integer plnPrdQty = ret_wo.getPln_prd_qty();
        //获得要绑内部订单的订单数量
        String hql2 = "From Ret_wo where wo_id = '" + woId2 + "'" + " and wo_typ = 'S'";
        List<Ret_wo> list2 = retWODao.find(hql2);
        Integer plantotal = 0;
        for (Ret_wo ret_wo2 : list2) {
            plantotal = ret_wo2.getPln_prd_qty();
        }

        //获得该内部订单已绑工单的订单数量
        String hql = "From Ret_wo where so_id = '" + woId2 + "'" + " and wo_typ = 'W'";
        List<Ret_wo> list = retWODao.find(hql);
        Integer total = 0;
        for (Ret_wo ret_wo2 : list) {
            Integer plnprdqty = ret_wo2.getPln_prd_qty();
            total = total + plnprdqty;
        }
        total = total + plnPrdQty;
        //判断设置后的数量和计划数量
        if (total > plantotal) {
            Integer over = total - plantotal;
            outTrx.setRtn_code(E_FDPAPLYWO_PRDQTY_ERROR + "");
            outTrx.setRtn_mesg("绑定工单的订单总数量[" + total + "]超出内部订单的订单数量[ " + plantotal + "]," + over.toString() + " 件，请选择其他内部订单");
            return;
        }*/

        ret_wo.setSo_id(ret_so.getWo_id());
        ret_wo.setEvt_usr(evtUsr);
        ret_wo.setEvt_timestamp(currentTime);
        retWODao.update(ret_wo);
        
        List<FdpaplywoOA> oaryList = new ArrayList<FdpaplywoOA>();
    	FdpaplywoOA oaryA = setOaryA(ret_wo);
        oaryList.add(oaryA);
        outTrx.setOary(oaryList);
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }

    private void queryByMtrl(FdpaplywoI inTrx, FdpaplywoO outTrx) {
        List<FdpaplywoIA> iary = inTrx.getIary();
        String mtrl_prod_id_fk = iary.get(0).getMtrl_prod_id_fk();
        String fromThickness = iary.get(0).getFrom_thickness();

        Double from_thickness = 0D;
        if (fromThickness != null) {
            from_thickness = Double.parseDouble(fromThickness);
        }
        String wo_cate = iary.get(0).getWo_cate();
        List<FdpaplywoOA> oaryList = new ArrayList<FdpaplywoOA>();

        String hql = "FROM Ret_wo where wo_typ='W' and mtrl_prod_id_fk = '" + mtrl_prod_id_fk +
                "' and from_thickness=" + from_thickness + " and wo_cate='" + wo_cate + "' and pln_prd_qty> rl_prd_qty order by pln_stb_timestamp";
        List<Ret_wo> ret_wos = retWODao.find(hql);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if (null != ret_wos && ret_wos.size() > 0) {
            String pst = sdf.format(ret_wos.get(0).getPln_stb_timestamp());
            String hql_prty = "FROM Ret_wo where wo_typ='W' and mtrl_prod_id_fk = '" + mtrl_prod_id_fk +
                    "' and from_thickness=" + from_thickness + " and wo_cate='" + wo_cate + "' and pln_prd_qty> rl_prd_qty "
                    + "and pln_stb_timestamp = " + "to_date('" + pst + "','yyyy-mm-dd hh24:mi:ss')" + " order by wo_prty";

            List<Ret_wo> ret_wo_prty = retWODao.find(hql_prty);
            if (null != ret_wo_prty && ret_wo_prty.size() > 0) {
                FdpaplywoOA fdpaplywoOA = new FdpaplywoOA();
                fdpaplywoOA.setWo_id(ret_wos.get(0).getWo_id());
                fdpaplywoOA.setMdl_id_fk(ret_wos.get(0).getMdl_id_fk());
                fdpaplywoOA.setPln_prd_qty(ret_wos.get(0).getPln_prd_qty());
                fdpaplywoOA.setRl_prd_qty(ret_wos.get(0).getRl_prd_qty());
                oaryList.add(fdpaplywoOA);
            }
        }
        outTrx.setOary(oaryList);
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }

    public void queryByDense(FdpaplywoI inTrx, FdpaplywoO outTrx) {
        List<FdpaplywoIA> iary = inTrx.getIary();
        String dense_id = iary.get(0).getDense_id();
        Ret_prd_in ret_prd_in = retPrdInDao.get(dense_id);
        if (Util.isSpaceCheck(ret_prd_in.getMtrl_prod_id_fk())) {
            outTrx.setRtn_code(E_FDPAPLYWO_OTHER_ERROR + "");
            outTrx.setRtn_mesg("dense mtrl_prod_id_fk is null");
            return;
        }
        if (Util.isSpaceCheck(ret_prd_in.getCus_id_fk())) {
            outTrx.setRtn_code(E_FDPAPLYWO_OTHER_ERROR + "");
            outTrx.setRtn_mesg("dense Cus_id_fk is null");
            return;
        }
        if (Util.isSpaceCheck(String.valueOf(ret_prd_in.getMtrl_thickness()))) {
            outTrx.setRtn_code(E_FDPAPLYWO_OTHER_ERROR + "");
            outTrx.setRtn_mesg("dense mtrl_thickness is null");
            return;
        }
        if (ret_prd_in.getPrd_act_qty() <= 0) {
            outTrx.setRtn_code(E_FDPAPLYWO_OTHER_ERROR + "");
            outTrx.setRtn_mesg("dense prd_act_qty is null");
            return;
        }
        List<FdpaplywoOB> oaryList = new ArrayList<FdpaplywoOB>();
        String hql = "FROM Ret_wo where wo_typ='W' and mtrl_prod_id_fk = '" + ret_prd_in.getMtrl_prod_id_fk() +
                "' and from_thickness=" + ret_prd_in.getMtrl_thickness() + " and pln_prd_qty> rl_prd_qty order by pln_stb_timestamp";

        List<Ret_wo> ret_wos = retWODao.find(hql);

        if (null != ret_wos && ret_wos.size() > 0) {
            String hql_prty = "FROM Ret_wo where wo_typ='W' and mtrl_prod_id_fk = '" + ret_prd_in.getMtrl_prod_id_fk() +
                    "' and from_thickness=" + ret_prd_in.getMtrl_thickness() + " and pln_prd_qty> rl_prd_qty "
                    + "and pln_stb_timestamp ='" + ret_wos.get(0).getPln_stb_timestamp() + "' order by wo_prty";
            List<Ret_wo> ret_wo_prty = retWODao.find(hql_prty);
            if (null != ret_wo_prty && ret_wo_prty.size() > 0) {
                FdpaplywoOB fdpaplywoOB = new FdpaplywoOB();
                fdpaplywoOB.setWo_id(ret_wos.get(0).getWo_id());
                fdpaplywoOB.setMdl_id_fk(ret_wos.get(0).getMdl_id_fk());
                fdpaplywoOB.setPln_prd_qty(ret_wos.get(0).getPln_prd_qty());
                fdpaplywoOB.setRl_prd_qty(ret_wos.get(0).getRl_prd_qty());
                fdpaplywoOB.setPrd_act_qty(ret_wos.get(0).getPln_prd_qty() - ret_wos.get(0).getRl_prd_qty());
                oaryList.add(fdpaplywoOB);
            }
        }
        outTrx.setOaryB(oaryList);
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }

    public void findAllList(FdpaplywoI inTrx, FdpaplywoO outTrx) {
        List<FdpaplywoIA> iarys = inTrx.getIary();
        List<String> woIds = new ArrayList<String>();
        for (FdpaplywoIA fdpaplywoIA : iarys) {
            String woId = fdpaplywoIA.getWo_id();
            woIds.add(woId);
        }

        String hql = "From Ret_wo where 1=1";
        String str = "";
        for (int i = 0; i < woIds.size() - 1; i++) {
            str = str + "'" + woIds.get(i).toString().trim() + "',";
        }
        str = str + "'" + woIds.get(woIds.size() - 1).toString().trim() + "'";
        if (woIds.size() > 0) {
            hql = hql + " and wo_id in (" + str + ")";
        }

        List<Ret_wo> ret_woList = retWODao.find(hql);
        List<FdpaplywoOA> oaryList = new ArrayList<FdpaplywoOA>();
        for (Ret_wo ret_wo:ret_woList) {
            FdpaplywoOA oaryA = setOaryA(ret_wo);
            oaryList.add(oaryA);
        }
        outTrx.setOary(oaryList);
        outTrx.setTbl_cnt(oaryList.size());
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }

    public void findWoIdList(FdpaplywoO outTrx) {
        String hql = "From Ret_wo where 1=1";
        List<Ret_wo> ret_wos = retWODao.find(hql);
        List<FdpaplywoOA> oaryList = new ArrayList<FdpaplywoOA>();
        for (Ret_wo ret_wo : ret_wos) {
            FdpaplywoOA fdpaplywoOA = new FdpaplywoOA();
            fdpaplywoOA.setWo_id(ret_wo.getWo_id());
            oaryList.add(fdpaplywoOA);
        }
        outTrx.setOary(oaryList);
        outTrx.setTbl_cnt(oaryList.size());
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }

    public void errActionFnc(FdpaplywoO outTrx) {
        outTrx.setRtn_code("0000009");
        outTrx.setRtn_mesg("Invalid Action_flg");
    }

    public String generateCode(String woCate) {
        String code = "";
        int i = 0;
        char[] Digit = {'1', '2', '3', '4', '5', '6', '7', '8', '9', 'A',
                'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S',
                'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};
        Timestamp currentTime = Util.getCurrentTimestamp();
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
        String time = sf.format(currentTime);
        String timeCode = String.join("", time.split("-")).substring(2);
        String cate = woCate.substring(0, 1);

        String likeCode = "L" + cate + timeCode + "A";
        String hql = "From Ret_wo where wo_id like '%" + likeCode + "%'";
        List<Ret_wo> ret_wos = retWODao.find(hql);
        if (ret_wos.size() > 0) {
            List<Byte> bytes = new ArrayList<Byte>();
            for (Ret_wo ret_wo : ret_wos) {
                String woId = ret_wo.getWo_id();
                char ch = woId.charAt(woId.length() - 1);
                byte b = (byte) ch;
                bytes.add(b);
            }
            byte mByte = Collections.max(bytes);
            char mch = (char) mByte;

            if (mch == Digit[8]) {
                mByte = 65;
                mch = (char) mByte;
                code = likeCode + mch;
                return code;
            }
            mByte++;
            mch = (char) mByte;
            code = likeCode + mch;
            return code;
        } else {
            i = 0;
            code = likeCode + Digit[i];
            return code;
        }
    }

    public void getInfoByWoId(FdpaplywoI inTrx, FdpaplywoO outTrx) {
        Ret_wo ret_wo = retWODao.get(inTrx.getWo_id());
        if (ret_wo == null) {
            outTrx.setRtn_code(E_RET_WO + E_READ_NOT_FOUND + "");
            outTrx.setRtn_mesg("工单不存在！");
            return;
        }
        List<FdpaplywoOA> oaryList = new ArrayList<FdpaplywoOA>();
        FdpaplywoOA oaryA = setOaryA(ret_wo);
        oaryList.add(oaryA);
        outTrx.setOary(oaryList);
        outTrx.setTbl_cnt(oaryList.size());
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }
    
    public void bindMtrlFnc (FdpaplywoI inTrx, FdpaplywoO outTrx) {
        String woId = inTrx.getWo_id();
        String evtUsr = inTrx.getEvt_usr();

        Timestamp currentTime = Util.getCurrentTimestamp();
        List<FdpaplywoIB> iaryBList = inTrx.getIaryB();
        List<Ret_wo_bom> ret_wo_boms = new ArrayList<>();
        //删除以前所有的绑定记录
        String mtrl_cate = inTrx.getMtrl_cate();
        if(!Util.isSpaceCheck(mtrl_cate)){
            String sql = "FROM Ret_wo_bom where 1=1";
            if (!Util.isSpaceCheck(woId)) {
                sql = sql + "and wo_id_fk='" + woId + "'";
            }
            if (!Util.isSpaceCheck(mtrl_cate)) {
                sql = sql + "and mtrl_cate='" +mtrl_cate + "'";
            }
            List<Ret_wo_bom>  ret_wo_bomList = retWoBomDao.find(sql);
            if(ret_wo_bomList.size()>0){
                retWoBomDao.deleteAll(ret_wo_bomList);
            }
        }
        if(iaryBList.size()>0){
            for(FdpaplywoIB fdpaplywoIB :iaryBList){
                Ret_wo_bom ret_wo_bom = new Ret_wo_bom();
                Ret_wo_bomId ret_wo_bomId = new Ret_wo_bomId();
                ret_wo_bomId.setWo_id_fk(woId);
                ret_wo_bomId.setMtrl_prod_id_fk(fdpaplywoIB.getMtrl_prod_id());
                ret_wo_bomId.setMtrl_cate(fdpaplywoIB.getMtrl_cate());
                ret_wo_bom.setId(ret_wo_bomId);

                String hql = " from Bis_data where data_cate='MTCT' and data_ext='"+fdpaplywoIB.getMtrl_cate()+"'";
                List<Bis_data> dataList = bisDataDao.find(hql);
                if(dataList.size()>0){
                    Bis_data  bis_data = dataList.get(0);
                    ret_wo_bom.setOpe_id_fk(bis_data.getExt_2());
                    ret_wo_bom.setMtrl_mode(bis_data.getExt_3());
                }
                Bis_mtrl bis_mtrl = bisMtrlDao.get(fdpaplywoIB.getMtrl_prod_id());
                if(bis_mtrl!=null){
                    ret_wo_bom.setMtrl_unit_fk(bis_mtrl.getMtrl_unit());
                }
                ret_wo_bom.setMtrl_use_qty(Double.valueOf(fdpaplywoIB.getMtrl_use_qty()));
                ret_wo_bom.setEvt_timestamp(currentTime);
                ret_wo_bom.setEvt_usr(evtUsr);
                ret_wo_boms.add(ret_wo_bom);
            }
        }
        if(ret_wo_boms.size()>0){
            retWoBomDao.saveOrUpdateAll(ret_wo_boms);
        }
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }
    
    public void  queryByMtrlCate (FdpaplywoI inTrx, FdpaplywoO outTrx) {
        List<FdpaplywoIB> iaryList = inTrx.getIaryB();
        if(iaryList.size()>0){
            FdpaplywoIB  iary = iaryList.get(0);
            String wo_id     = iary.getWo_id();
            String mtrl_cate = iary.getMtrl_cate();
            String hql = "FROM Ret_wo_bom where 1=1";
            if (!Util.isSpaceCheck(wo_id)) {
                hql = hql + "and wo_id_fk='" + wo_id + "'";
            }
            if (!Util.isSpaceCheck(mtrl_cate)) {
                hql = hql + "and mtrl_cate='" +mtrl_cate + "'";
            }
            List<Ret_wo_bom>  ret_wo_boms = retWoBomDao.find(hql);
            List<FdpaplywoOC> oaryList = new ArrayList<>();
            for(Ret_wo_bom ret_wo_bom :ret_wo_boms){
                FdpaplywoOC oary = new FdpaplywoOC();
                oary.setWo_id_fk(ret_wo_bom.getId().getWo_id_fk());
                oary.setMtrl_cate(ret_wo_bom.getId().getMtrl_cate());
                oary.setMtrl_prod_id(ret_wo_bom.getId().getMtrl_prod_id_fk());
                oary.setMtrl_use_qty(String.valueOf(ret_wo_bom.getMtrl_use_qty()));
                oaryList.add(oary);
            }
            outTrx.setOaryC(oaryList);
        }
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }
    public void  sapWoMtrl(FdpaplywoI inTrx, FdpaplywoO outTrx) {

        String woId = "";
        List<FdpaplywoIB> iaryList = inTrx.getIaryB();
        Timestamp currentTime = Util.getCurrentTimestamp();
        if(iaryList!=null && iaryList.size()>0){
            Map<Integer,String> map = new HashMap<Integer,String>();
            for(int i=0;i<iaryList.size();i++){
                if(!map.values().contains(iaryList.get(i).getWo_id())){
                    map.put(i,iaryList.get(i).getWo_id());
                }
            }
            for (Map.Entry<Integer,String> entry : map.entrySet()) {
                String sql = "FROM Ret_wo_bom where wo_id_fk='" + entry.getValue() + "'";
                List<Ret_wo_bom>  ret_wo_bomList = retWoBomDao.find(sql);
                if(ret_wo_bomList.size()>0){
                    retWoBomDao.deleteAll(ret_wo_bomList);
                }
            }
            List<Ret_wo_bom>  retWoBoms = new ArrayList<>();
            for(FdpaplywoIB fdpaplywoIB :iaryList){
                String  mtrl_prod_id = fdpaplywoIB.getMtrl_prod_id();
                Bis_mtrl bis_mtrl = bisMtrlDao.get(mtrl_prod_id);
                String  mtrl_cate="";
                if(bis_mtrl!=null){
                    mtrl_cate = bis_mtrl.getMtrl_cate();
                }else{
                    //物料基础信息不存在
                    outTrx.setRtn_code(E_FBPBISMTR_IARY_IS_NULL+"");
                    outTrx.setRtn_mesg("物料号["+mtrl_prod_id+"]物料基础信息不存在");
                    return;
                }
                Ret_wo_bom ret_wo_bom = new Ret_wo_bom();
                Ret_wo_bomId ret_wo_bomId = new Ret_wo_bomId();
                ret_wo_bomId.setMtrl_prod_id_fk(mtrl_prod_id);
                ret_wo_bomId.setMtrl_cate(mtrl_cate);
                ret_wo_bomId.setWo_id_fk(fdpaplywoIB.getWo_id());
                ret_wo_bom.setId(ret_wo_bomId);

                ret_wo_bom.setActive_flg(fdpaplywoIB.getActive_flg());
                ret_wo_bom.setMtrl_type(fdpaplywoIB.getMtrl_type());
                ret_wo_bom.setMtrl_mode(fdpaplywoIB.getMtrl_mode());
                ret_wo_bom.setMtrl_unit_fk(fdpaplywoIB.getMtrl_unit());
                ret_wo_bom.setMtrl_use_qty(Double.valueOf(fdpaplywoIB.getMtrl_use_qty()));
                String hqlM = " from Bis_data where data_cate='MTCT' and data_ext='"+mtrl_cate+"'";
                List<Bis_data> dataList = bisDataDao.find(hqlM);
                if(dataList!=null && dataList.size()>0){
                    Bis_data  bis_data = dataList.get(0);
                    ret_wo_bom.setOpe_id_fk(bis_data.getExt_2());
                }else{
                    //物料使用站点信息不存在
                    outTrx.setRtn_code(E_FBPBISMTR_OPE_IS_NULL+"");
                    outTrx.setRtn_mesg("物料号["+mtrl_prod_id+"]物料使用站点信息不存在");
                    return;
                }
                ret_wo_bom.setItem_no(fdpaplywoIB.getItem_no());
                ret_wo_bom.setRsv_no(fdpaplywoIB.getRsv_no());
                ret_wo_bom.setFab_id_fk(fdpaplywoIB.getFab_id());
                ret_wo_bom.setDest_shop_fk(fdpaplywoIB.getDest_shop());
                ret_wo_bom.setUnq_seq_id(GUIDGenerator.javaGUID());
                ret_wo_bom.setEvt_usr("SAP");
                ret_wo_bom.setEvt_timestamp(currentTime);
                retWoBoms.add(ret_wo_bom);
            }
            retWoBomDao.saveOrUpdateAll(retWoBoms);
        }
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return;
    }
    //Set Wo info
    private FdpaplywoOA setOaryA(Ret_wo ret_wo){
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//定义格式，不显示毫秒
    	FdpaplywoOA oaryA = new FdpaplywoOA();
    	oaryA.setCrt_timestamp(ret_wo.getCrt_timestamp());
        oaryA.setWo_id(ret_wo.getWo_id());
        oaryA.setSo_id(ret_wo.getSo_id());
        oaryA.setWo_stat(ret_wo.getWo_stat());
        oaryA.setCus_id_fk(ret_wo.getCus_id_fk());
        oaryA.setMtrl_prod_id_fk(ret_wo.getMtrl_prod_id_fk());
        oaryA.setMdl_id_fk(ret_wo.getMdl_id_fk());
        oaryA.setPln_prd_qty(ret_wo.getPln_prd_qty());//计划数量
        oaryA.setWh_in_prd_qty(ret_wo.getWh_in_prd_qty());//入库数量
        oaryA.setWh_out_prd_qty(ret_wo.getWh_out_prd_qty());//出库数量
        oaryA.setRl_prd_qty(ret_wo.getRl_prd_qty());//下线数量
        oaryA.setComp_prd_qty(ret_wo.getComp_prd_qty());//完成数量
        oaryA.setScrp_prd_qty(ret_wo.getScrp_prd_qty());//报废数量
        oaryA.setWo_prty(ret_wo.getWo_prty());
        if (ret_wo.getPln_cmp_timestamp() != null) {
            oaryA.setPln_cmp_timestamp(df.format(ret_wo.getPln_cmp_timestamp()));
        }
        if (ret_wo.getPln_stb_timestamp() != null) {
            oaryA.setPln_stb_timestamp(df.format(ret_wo.getPln_stb_timestamp()));
        }
        oaryA.setPath_id_fk(ret_wo.getPath_id_fk());
        oaryA.setPath_ver_fk(ret_wo.getPath_ver_fk());
        oaryA.setFab_site_fk(ret_wo.getFab_site_fk());
        oaryA.setFab_id_fk(ret_wo.getFab_id_fk());
        oaryA.setWo_note(ret_wo.getWo_note());
        oaryA.setWo_dsc(ret_wo.getWo_dsc());
        oaryA.setWo_cate(ret_wo.getWo_cate());
        oaryA.setMtrl_lot_id_fk(ret_wo.getMtrl_lot_id_fk());
    	return oaryA;
    }
}
